package br.org.group.android.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import br.org.group.android.app.ConfigurationVO;

public class FortunesDbAdapter {

	public static final String KEY_ROWID = "_id";
	public static final String KEY_MSG = "mensagem";
	public static final String KEY_AUTHOR = "autor";
	public static final String KEY_STATUS = "lido";

	public final static String FORTUNES_TABLE = "Fortunes";
	public final static String DB_FORTUNES = "FortunesDB";
	
	public static final String KEY_CONFIG = "config";
	public static final String KEY_CONFIG_VALUE = "value";
	
	public final static String CONFIGURATIONS_TABLE = "Configuracoes";

	private String[] columns = new String[] { KEY_ROWID, KEY_MSG, KEY_AUTHOR,
			KEY_STATUS };
	private Context ctx;
	private SQLiteDatabase db;
	private SQLiteHelper dbFortune;
	private final int DB_VERSION = 25;
	private static final String DELETE_FORTUNES = "DROP TABLE IF EXISTS "
		+ FORTUNES_TABLE;
	private static final String DELETE_CONFIGURATIONS_TABLE = "DROP TABLE IF EXISTS "
		+ CONFIGURATIONS_TABLE;

	private static final String SCRIPT_CREATE_FORTUNES = "CREATE TABLE IF NOT EXISTS "
		+ FORTUNES_TABLE
		+ " ("
		+ KEY_ROWID
		+ " integer primary key autoincrement,"
		+ KEY_MSG
		+ " text not null, "
		+ KEY_AUTHOR
		+ " text not null,"
		+ KEY_STATUS
		+ " int not null);";

	private static final String SCRIPT_CREATE_CONFIGURATIONS = "CREATE TABLE IF NOT EXISTS "
		+ CONFIGURATIONS_TABLE
		+ " ("
		+ KEY_CONFIG
		+ " Integer not null,"
		+ KEY_CONFIG_VALUE
		+ " Integer not null);";

	private class SQLiteHelper extends SQLiteOpenHelper {

		public SQLiteHelper(Context ctx) {
			super(ctx, DB_FORTUNES, null, DB_VERSION);
		}

		public void onCreate(SQLiteDatabase db) {
			db.execSQL(SCRIPT_CREATE_FORTUNES);
			db.execSQL(SCRIPT_CREATE_CONFIGURATIONS);
			inserirConfiguracao(db,new ConfigurationVO(ConfigurationVO.CONFIG_ICONS, ConfigurationVO.ICONS_NEW));
			inserirConfiguracao(db,new ConfigurationVO(ConfigurationVO.CONFIG_SHARE, ConfigurationVO.SHARE_ALWAYS_ASK));
			
			ImportFortunes impFortunes = new ImportFortunes();
			impFortunes.Popula(db, ctx);
		}

		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			db.execSQL(DELETE_FORTUNES);
			db.execSQL(DELETE_CONFIGURATIONS_TABLE);
			
			onCreate(db);
		}
	}

	public FortunesDbAdapter(Context ctx) {
		this.ctx = ctx;
		// this.open();
		// this.close();
	}

	public long inserir(FortunesDAO f) {
		this.open();
		ContentValues cv = new ContentValues();
		cv.put(KEY_MSG, f.getMensagem());
		cv.put(KEY_AUTHOR, f.getAutor());
		cv.put(KEY_STATUS, f.getStatus());

		long ret = db.insert(FORTUNES_TABLE, null, cv);
		this.close();
		return ret;
	}
	
	private long inserirConfiguracao(SQLiteDatabase db,ConfigurationVO c) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_CONFIG, c.getConfig());
		cv.put(KEY_CONFIG_VALUE, c.getValue());

		long ret = db.insert(CONFIGURATIONS_TABLE, null, cv);
		return ret;
	}

	public void atualizarLido(FortunesDAO f) {
		f.setLido(FortunesDAO.LIDO);
		atualizar(f);
	}

	public long atualizar(FortunesDAO f) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_MSG, f.getMensagem());
		cv.put(KEY_AUTHOR, f.getAutor());
		cv.put(KEY_STATUS, f.getStatus());
		return db.update(FORTUNES_TABLE, cv, FortunesDbAdapter.KEY_ROWID
				+ " = ?", new String[] { String.valueOf(f.getId()) });
	}
	
	public long atualizarConfiguracao(ConfigurationVO c) {
		this.open();
		ContentValues cv = new ContentValues();
		cv.put(KEY_CONFIG, c.getConfig());
		cv.put(KEY_CONFIG_VALUE, c.getValue());
		long res = db.update(CONFIGURATIONS_TABLE, cv, FortunesDbAdapter.KEY_CONFIG
				+ " = ?", new String[] { String.valueOf(c.getConfig()) });
		this.close();
		return res;
	}

	public int excluir(int id) {
		return db.delete(FORTUNES_TABLE, FortunesDbAdapter.KEY_ROWID + " = ?",
				new String[] { String.valueOf(id) });
	}

	public List<FortunesDAO> buscarTodasFortunes(String nome) {
		List<FortunesDAO> lista = new ArrayList<FortunesDAO>();

		String[] args = new String[] { nome + "%" };

		Cursor c = db.query(FORTUNES_TABLE, columns, KEY_MSG + " like ? and "
				+ KEY_STATUS + " = " + FortunesDAO.NAO_LIDO, args, null, null,
				KEY_MSG);

		c.moveToFirst();
		while (!c.isAfterLast()) {
			FortunesDAO fortune = fillFortune(c);
			lista.add(fortune);
			c.moveToNext();
		}
		c.close();
		return lista;

	}

	public FortunesDAO retornaFortune() {
		this.open();
		FortunesDAO fortune = new FortunesDAO();
		int passo = 1;
		int registro = (new Random( Calendar.getInstance().getTimeInMillis())).nextInt()%totalRegistros();

		String[] args = new String[] { String.valueOf(registro) };

		while (!(fortune.getId() > 0)) {
			Cursor c;

			if (passo == 1) {
				c = db.query(FORTUNES_TABLE, columns, KEY_STATUS + " = "
						+ FortunesDAO.NAO_LIDO + "  and "
						+ FortunesDbAdapter.KEY_ROWID + " > ?", args, null,
						null, KEY_MSG);
			} else {
				c = db.query(FORTUNES_TABLE, columns, KEY_STATUS + " = "
						+ FortunesDAO.NAO_LIDO + " and "
						+ FortunesDbAdapter.KEY_ROWID + " <= ?", args, null,
						null, KEY_MSG);
			}
			passo++;

			c.moveToFirst();
			if (!c.isAfterLast()) {
				fortune = fillFortune(c);
				this.atualizarLido(fortune);
			} else if (passo == 3) {
				db.execSQL("update " + FORTUNES_TABLE + " set " + KEY_STATUS
						+ " = " + FortunesDAO.NAO_LIDO);
				passo = 1;
			}
			c.close();

		}
		this.close();
		return fortune;

	}

	public FortunesDAO getFortune(int id){
		FortunesDAO fDao = null;
		open();
		Cursor c = db.rawQuery("select * from " + FORTUNES_TABLE+" where "+FortunesDbAdapter.KEY_ROWID+"="+String.valueOf(id), null);

		c.moveToFirst();
		if (!c.isAfterLast()) {
			fDao = fillFortune(c);
		}
		c.close();
		close();
		return fDao;
		
	}
	public ConfigurationVO getConfiguration(int key){
		ConfigurationVO config = null;
		open();
		Cursor c = db.rawQuery("select * from " + CONFIGURATIONS_TABLE+" where "+KEY_CONFIG+"="+key, null);

		c.moveToFirst();
		if (!c.isAfterLast()) {
			config = new ConfigurationVO(key, c.getInt(c.getColumnIndex(KEY_CONFIG_VALUE)));
		}
		c.close();
		close();
		return config;
		
	}
	
	private FortunesDAO fillFortune(Cursor c) {
		FortunesDAO fortune = new FortunesDAO();
		fortune.setId((int) c.getLong(0));
		fortune.setMensagem(c.getString(1));
		fortune.setAutor(c.getString(2));
		fortune.setLido(c.getInt(3));
		return fortune;
	}

	private int totalRegistros() {
		int resultado = 0;

		Cursor c = db.rawQuery("select count(1) from " + FORTUNES_TABLE, null);

		c.moveToFirst();
		if (!c.isAfterLast()) {
			resultado = (int) c.getLong(0);
		}
		c.close();

		return resultado;
	}

	public int getMaxId() {
		int resultado = 0;
		this.open();
		// TODO: ARRUMAR ESSA CONSULTA E NÂO FICAR CONFIANDO EM totalRegistros()
		// Cursor c = db.rawQuery("select max(1) from " + FORTUNES_TABLE, null);

		// c.moveToFirst();
		// resultado = (int) c.getLong(0);
		// c.close();
		resultado = totalRegistros();
		this.close();
		return resultado;
	}

	private FortunesDbAdapter open() {
		dbFortune = new SQLiteHelper(ctx);
		db = dbFortune.getWritableDatabase();
		return this;
	}

	private void close() {
		db.close();

	}

	public boolean alreadyHasFortune(FortunesDAO fdao) {
		return fdao.getId() <= getMaxId();
	}
}
